﻿
-- Create extended property update procedure
CREATE PROCEDURE ExtProps.PropUpdate
	@Object_Name sysname,
	@Property_Name sysname,
	@Property_Value sql_variant
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @Object_Id integer,
		@Object_Type sysname,
		@Part1 sysname,
		@Part2 sysname,
		@Part3 sysname,
		@Part4 sysname,
		@Database_Name sysname,
		@Schema_Name sysname, 
		@Schema_Object_Name sysname, 
		@Column_Name sysname,
		@ERR_MSG varchar(8000),
		@ERR_STA tinyint,
		@ERR_SEV smallint;

	SELECT @Part1 = PARSENAME(@Object_Name, 1),
		@Part2 = PARSENAME(@Object_Name, 2),
		@Part3 = PARSENAME(@Object_Name, 3),
		@Part4 = PARSENAME(@Object_Name, 4),
		@Property_Name = COALESCE(@Property_Name, N'MS_Description');

	IF @Part4 IS NOT NULL
	BEGIN
		RAISERROR (N'Cannot specify 4-part names in PropUpdate.', 18, 127)
		WITH NOWAIT, SETERROR;
		IF XACT_STATE() <> 0
			ROLLBACK TRANSACTION;
		RETURN;
	END;

	IF @Part1 IS NULL AND @Part2 IS NULL AND @Part3 IS NULL AND @Part4 IS NULL
	BEGIN -- @Object_Type is DATABASE
		BEGIN TRY
			EXECUTE sys.sp_updateextendedproperty
				@name = @Property_Name,
				@value= @Property_Value;
		END TRY
		BEGIN CATCH
			SET @ERR_SEV = ERROR_SEVERITY();
			SET @ERR_STA = ERROR_STATE();
			SET @ERR_MSG = ERROR_MESSAGE() + ' Error occurred while updating property on the current database.';
			RAISERROR(@ERR_MSG, @ERR_SEV, @ERR_STA);
			IF XACT_STATE() <> 0
				ROLLBACK TRANSACTION;
			RETURN;
		END CATCH
	END
	ELSE IF @Part2 IS NULL AND @Part3 IS NULL AND @Part4 IS NULL
	BEGIN
		SET @Object_Type = N'SCHEMA';
		SELECT @Schema_Name = @Part1;
		BEGIN TRY
			EXECUTE sys.sp_updateextendedproperty 
				@name = @Property_Name, 
				@value = @Property_Value,
				@level0type = @Object_Type, 
				@level0name = @Schema_Name;
		END TRY
		BEGIN CATCH
			SET @ERR_SEV = ERROR_SEVERITY();
			SET @ERR_STA = ERROR_STATE();
			SET @ERR_MSG = ERROR_MESSAGE() + ' Error occurred while updating property on the [' + @Schema_Name + '] schema.';
			RAISERROR(@ERR_MSG, @ERR_SEV, @ERR_STA);
			IF XACT_STATE() <> 0
				ROLLBACK TRANSACTION;
			RETURN;
		END CATCH
	END
	ELSE IF @Part3 IS NULL AND @Part4 IS NULL
	BEGIN
		SET @Object_Id = OBJECT_ID(@Object_Name);
		SELECT @Object_Type =
			CASE WHEN type IN (N'U', N'IT', N'S') THEN N'TABLE'
				WHEN type = N'V' THEN N'VIEW'
				WHEN type IN (N'P', N'X', N'PC') THEN N'PROCEDURE'
				WHEN type = N'AF' THEN N'AGGREGATE'
				WHEN type IN (N'FT', N'FN', N'IF', N'TF') THEN N'FUNCTION'
				WHEN type = N'SN' THEN N'SYNONYM'
				WHEN type = N'SQ' THEN N'QUEUE'
				END,
			@Schema_Object_Name = @Part1,
			@Schema_Name = @Part2
		FROM sys.objects
		WHERE object_id = @Object_Id;
		BEGIN TRY
			EXECUTE sys.sp_updateextendedproperty 
				@name = @Property_Name,
				@value = @Property_Value,
				@level0type = N'SCHEMA', 
				@level0name = @Schema_Name,
				@level1type = @Object_Type, 
				@level1name = @Schema_Object_Name; 
		END TRY
		BEGIN CATCH
			SET @ERR_SEV = ERROR_SEVERITY();
			SET @ERR_STA = ERROR_STATE();
			SET @ERR_MSG = ERROR_MESSAGE() + ' Error occurred while updating property on the [' + @Schema_Name + '].[' + @Schema_Object_Name + '] ' + COALESCE(@Object_Type, '') + '.';
			RAISERROR(@ERR_MSG, @ERR_SEV, @ERR_STA);
			IF XACT_STATE() <> 0
				ROLLBACK TRANSACTION;
			RETURN;
		END CATCH
	END
	ELSE IF @Part4 IS NULL 
	BEGIN
		SELECT @Column_Name = @Part1,
			@Schema_Object_Name = @Part2,
			@Schema_Name = @Part3;

		BEGIN TRY
			SET @Object_Id = OBJECT_ID(N'[' + @Schema_Name + '].[' + @Schema_Object_Name + N']');
			SELECT @Object_Type =
				CASE WHEN type IN (N'U', N'IT', N'S') THEN N'TABLE'
					WHEN type = N'V' THEN N'VIEW'
					WHEN type IN (N'P', N'X', N'PC') THEN N'PROCEDURE'
					WHEN type = N'AF' THEN N'AGGREGATE'
					WHEN type IN (N'FT', N'FN', N'IF', N'TF') THEN N'FUNCTION'
					WHEN type = N'SN' THEN N'SYNONYM'
					WHEN type = N'SQ' THEN N'QUEUE'
					END
			FROM sys.objects
			WHERE object_id = @Object_Id;

			EXECUTE sys.sp_updateextendedproperty 
				@name = @Property_Name,
				@value = @Property_Value,
				@level0type = N'SCHEMA', 
				@level0name = @Schema_Name,
				@level1type = @Object_Type, 
				@level1name = @Schema_Object_Name, 
				@level2type = N'COLUMN',
				@level2name = @Column_Name;
		END TRY
		BEGIN CATCH
			SET @ERR_SEV = ERROR_SEVERITY();
			SET @ERR_STA = ERROR_STATE();
			SET @ERR_MSG = ERROR_MESSAGE() + ' Error occurred while updating property on the [' + @Schema_Name + '].[' + @Schema_Object_Name + '].[' + @Column_Name + '] column.';
			RAISERROR(@ERR_MSG, @ERR_SEV, @ERR_STA);
			IF XACT_STATE() <> 0
				ROLLBACK TRANSACTION;
			RETURN;
		END CATCH
	END
END